from flask import Blueprint, render_template, request, session, redirect, url_for
from .db import get_db
from .decorator import login_required

student_bp = Blueprint('student', __name__)

@student_bp.route('/student', methods=['GET'])
@login_required
def index():
    students = get_db().execute("SELECT student.id as id, student.name as name, student.username as username, student.sex as sex, student.birthday as birthday, clazz.name as clazz_name FROM student left join `clazz` on student.clazz_id = clazz.id order by `id` desc").fetchall()
    return render_template('student/index.html', students=students)

@student_bp.route('/student/add', methods=['GET'])
@login_required
def add():
    clazzes = get_db().execute("select * from `clazz` order by `name`").fetchall()
    return render_template('student/add.html', clazzes=clazzes)


@student_bp.route('/student/edit', methods=['GET'])
@login_required
def edit():
    id = int(request.args.get('id'))
    student = get_db().execute("SELECT student.id as id, student.clazz_id as clazz_id, student.name as name, student.username as username, student.sex as sex, student.birthday as birthday, clazz.name as clazz_name FROM student left join `clazz` on student.clazz_id = clazz.id where student.id = ?", (id,)).fetchone()
    clazzes = get_db().execute("select * from `clazz` order by `name`").fetchall()
    return render_template('student/edit.html', clazzes=clazzes, student=student)

@student_bp.route('/student/delete', methods=['GET'])
@login_required
def delete():
    id = request.args.get('id')
    get_db().execute("delete from student where id=?", (id,))
    get_db().commit()
    return redirect(url_for('student.index'))

@student_bp.route('/student/save', methods=['POST'])
@login_required
def save():
    name = request.form['name']
    sex = request.form['sex']
    birthday = request.form['birthday']
    username = request.form['username']
    clazz_id = request.form['clazz_id']
    password = 'tute'
    get_db().execute('insert into student (name, sex, birthday, username, clazz_id, password) values (?, ?, ?, ?, ?, ?)',
                     (name, sex, birthday, username, clazz_id, password))
    get_db().commit()
    return redirect(url_for('student.index'))

@student_bp.route('/student/update', methods=['POST'])
@login_required
def update():
    id = request.form['id']
    name = request.form['name']
    sex = request.form['sex']
    birthday = request.form['birthday']
    username = request.form['username']
    clazz_id = request.form['clazz_id']
    get_db().execute('update student set name=?, sex=?, birthday=?, username=?, clazz_id=? where id=?', (name,sex,birthday,username,clazz_id,id,))
    get_db().commit()
    return redirect(url_for('student.index'))
